#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
修复数据库 ID 字段自增问题
"""

import sys
sys.path.append('src')

from database import db_manager

def fix_id_autoincrement():
    """
    修复 characters 表的 id 字段为自增主键
    """
    try:
        # 检查当前表结构
        print("检查当前 characters 表结构...")
        result = db_manager.execute_query("DESCRIBE characters")
        for row in result:
            if row['Field'] == 'id':
                print(f"id 字段: 类型={row['Type']}, 键={row['Key']}, 额外={row['Extra']}")
        
        # 检查外键约束
        print("\n检查外键约束...")
        fk_query = """
            SELECT 
                CONSTRAINT_NAME,
                TABLE_NAME,
                COLUMN_NAME,
                REFERENCED_TABLE_NAME,
                REFERENCED_COLUMN_NAME
            FROM 
                INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
            WHERE 
                REFERENCED_TABLE_NAME = 'characters'
                AND TABLE_SCHEMA = DATABASE()
        """
        
        foreign_keys = db_manager.execute_query(fk_query)
        
        # 临时删除外键约束
        dropped_fks = []
        for fk in foreign_keys:
            constraint_name = fk['CONSTRAINT_NAME']
            table_name = fk['TABLE_NAME']
            print(f"删除外键约束: {table_name}.{constraint_name}")
            
            drop_fk_query = f"ALTER TABLE {table_name} DROP FOREIGN KEY {constraint_name}"
            db_manager.execute_update(drop_fk_query)
            dropped_fks.append(fk)
        
        # 删除主键
        print("\n删除现有主键...")
        db_manager.execute_update("ALTER TABLE characters DROP PRIMARY KEY")
        print("主键删除成功")
        
        # 修改 id 字段类型并设置为自增主键
        print("\n修改 id 字段为自增主键...")
        alter_query = """
            ALTER TABLE characters 
            MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY
        """
        
        db_manager.execute_update(alter_query)
        print("id 字段修改成功")
        
        # 重新创建外键约束
        print("\n重新创建外键约束...")
        for fk in dropped_fks:
            constraint_name = fk['CONSTRAINT_NAME']
            table_name = fk['TABLE_NAME']
            column_name = fk['COLUMN_NAME']
            referenced_column = fk['REFERENCED_COLUMN_NAME']
            
            # 由于 id 字段类型改变，需要先修改引用表的字段类型
            print(f"修改 {table_name}.{column_name} 字段类型为 INT")
            modify_ref_query = f"ALTER TABLE {table_name} MODIFY COLUMN {column_name} INT"
            db_manager.execute_update(modify_ref_query)
            
            # 重新创建外键
            add_fk_query = f"""
                ALTER TABLE {table_name} 
                ADD CONSTRAINT {constraint_name} 
                FOREIGN KEY ({column_name}) REFERENCES characters({referenced_column})
            """
            db_manager.execute_update(add_fk_query)
            print(f"重新创建外键约束: {table_name}.{constraint_name}")
        
        # 验证修改结果
        print("\n验证修改结果...")
        result = db_manager.execute_query("DESCRIBE characters")
        for row in result:
            if row['Field'] == 'id':
                print(f"id 字段: 类型={row['Type']}, 键={row['Key']}, 额外={row['Extra']}")
        
        print("\n数据库修复完成！")
        
    except Exception as e:
        print(f"修复失败: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    fix_id_autoincrement()